﻿using System.Text;
using System.Collections.ObjectModel;
using MarginTrading.Entity;
using MarginTrading.Common;
using System.Data.OracleClient;
using System.Data;
using System.Collections.Generic;

namespace MarginTrading.Services
{
    public class SecurityServices
    {
        private static Security GetSecurityFromDataReader(IDataReader dataReader)
        {
            Security retVal = null;
            if (dataReader != null && !dataReader.IsClosed)
            {
                retVal = new Security();
                retVal.Symbol = dataReader["SYMBOL"].ToString();
                retVal.Name = dataReader["NAME"].ToString();
                double marketValue = 0;
                if (double.TryParse(dataReader["MARKET_VALUE"].ToString(), out marketValue))
                    retVal.MarketValue = marketValue;
                double marginPercentage = 0;
                if (double.TryParse(dataReader["MARGIN_PERCENTAGE"].ToString(), out marginPercentage))
                    retVal.MarginPercentage = marginPercentage;
            }
            return retVal;
        }

        public static Security GetSecurityBySymbol(string symbol)
        {
            Security retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select * from security where symbol = '" + symbol+ "'";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader.Read())
                    {
                        retVal = GetSecurityFromDataReader(dataReader);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static SecurityCollection GetSecurityCollection()
        {
            SecurityCollection retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select * from security";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    retVal = new SecurityCollection();
                    while (dataReader.Read())
                    {
                        Security item = GetSecurityFromDataReader(dataReader);
                        if (item != null)
                            retVal.Add(item);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }


        public static List<string> GetSecSymbolList()
        {
            List<string> retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select SYMBOL from security";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    retVal = new List<string>();
                    while (dataReader.Read())
                    {
                        string item =  dataReader["SYMBOL"].ToString();
                        if (!string.IsNullOrEmpty(item))
                            retVal.Add(item);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }



        public static DataTable GetSecurityCollectionDataTable()
        {
            DataTable retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select * from security order by symbol";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader != null)
                    {
                        retVal = new DataTable();
                        retVal.Load(dataReader);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static void Add(Security security)
        {
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "insert into security(SYMBOL,NAME,MARKET_VALUE,MARGIN_PERCENTAGE)" +
                                  "values(:v1,:v2,:v3,:v4)";
                cmd.Parameters.Add(":v1", OracleType.VarChar).Value = security.Symbol;
                cmd.Parameters.Add(":v2", OracleType.VarChar).Value = security.Name;
                cmd.Parameters.Add(":v3", OracleType.Number).Value = security.MarketValue;
                cmd.Parameters.Add(":v4", OracleType.Number).Value = security.MarginPercentage;

                cmd.Connection = con;
                cmd.ExecuteNonQuery();
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
        }

        public static void Delete(string symbol)
        {
            OracleConnection con = new OracleConnection();
            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "delete from security where symbol = '" + symbol + "'";
                cmd.Connection = con;

                cmd.ExecuteNonQuery();
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
        }

        public static void Update(Security security)
        {
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "update security set " +
                                  "NAME = :v2, " +
                                  "MARKET_VALUE = :v3, " +
                                  "MARGIN_PERCENTAGE = :v4 " +
                                  "where SYMBOL = :v1";
                cmd.Parameters.Add(":v1", OracleType.VarChar).Value = security.Symbol;
                cmd.Parameters.Add(":v2", OracleType.VarChar).Value = security.Name;
                cmd.Parameters.Add(":v3", OracleType.Number).Value = security.MarketValue;
                cmd.Parameters.Add(":v4", OracleType.Number).Value = security.MarginPercentage;

                cmd.Connection = con;
                cmd.ExecuteNonQuery();

            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
        }
    }
}
